<%@ page contentType="text/html; charset=UTF-8" language="java" %>
<%@ page import="com.base.myproject.server.tools.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.util.Date" %>
<%@page import="java.text.SimpleDateFormat,java.util.Calendar,java.math.BigDecimal"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<title></title>
<LINK href="/hr_Jsp/report.css" rel=stylesheet>
</head>
<body>
<%
	String excel = request.getParameter("excel");
	
	if (excel != null && !excel.equals("")) {
		response.setHeader("Content-disposition","attachment; filename=monthTurnover.xls");
	}

	String month = request.getParameter("yearMonth");
	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
	Calendar canlandar = Calendar.getInstance();
	Date onDate = null;
	String onMonth = "";
	if (month == null) {
		Date defaultDate = new Date();
		String date = format.format(defaultDate);
		month = date;
	}
	DataSetTool DST = new DataSetTool();
	//不包括实习生的流失率统计
	String depSql = "select system.name,expectedSum,onSum,isnull(leavNo.num1,0) as leavNo,isnull(entryNo.num2,0) as entryNo,thisSum,(expectedSum - thisSum) as vacancies, (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as staff from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') >= '" + month + "' and CONVERT(varchar(7), entrydate, 120) < '" + month + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "') group by substring(systemid,1,4)) as onMonthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + month + "' and CONVERT(varchar(7), entrydate, 120) <= '" + month + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "') group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = onMonthSum.systemid inner join (select substring(department,1,4) as systemid,isnull(sum(persion_num),0) as expectedSum from job_table group by substring(department,1,4)) as expectedAllSum on expectedAllSum.systemid = onMonthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where CONVERT(varchar(7), lastjobdate, 120) = '" + month + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "') group by substring(systemid,1,4)) as leavNo on leavNo.systemid = onMonthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num2 from bcode_job a inner join bcode b on b.bcode = a.bcode where employstatus='在职' and CONVERT(varchar(7), entrydate, 120) = '" + month + "' and isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "') group by substring(systemid,1,4)) as entryNo on entryNo.systemid = onMonthSum.systemid left join (select name,id from systeminfo where len(id) = 4) as system on system.id = onMonthSum.systemid";
	//实习生的流失率统计
	String internsSql = "select onMonthSum.systemid,expectedSum,onSum,isnull(leavNo.num1,0) as leavNo,isnull(entryNo.num2,0) as entryNo,thisSum,(expectedSum - thisSum) as vacancies, case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as staff from  " + 
							"( " + 
							"select '实习生' as systemid,count(*) as onSum  " + 
							"from bcode_job a inner join bcode b on b.bcode = a.bcode  " + 
							"where isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') >= '" + month + "'  " + 
							"and CONVERT(varchar(7), entrydate, 120) < '" + month + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "')) " + 
							") as onMonthSum  " + 
							"inner join  " + 
							"( " + 
							"select '实习生' as systemid,count(*) as thisSum  " + 
							"from bcode_job a inner join bcode b on b.bcode = a.bcode  " + 
							"where isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + month + "'  " + 
							"and CONVERT(varchar(7), entrydate, 120) <= '" + month + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "')) " + 
							") as thisMonthSum  " + 
							"on thisMonthSum.systemid = onMonthSum.systemid  " + 
							"inner join  " + 
							"( " + 
							"select '实习生' as systemid,isnull(sum(traininger_num),0) as expectedSum  " + 
							"from job_table where traininger_num is not null  " + 
							") as expectedAllSum on expectedAllSum.systemid = onMonthSum.systemid  " + 
							"left join  " + 
							"( " + 
							"select '实习生' as systemid,count(*) as num1  " + 
							"from bcode_job a inner join bcode b on b.bcode = a.bcode where " + 
							"CONVERT(varchar(7), lastjobdate, 120) = '" + month + "' and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "')) " + 
							") as leavNo on leavNo.systemid = onMonthSum.systemid  " + 
							"left join  " + 
							"( " + 
							"select '实习生' as systemid,count(*) as num2  " + 
							"from bcode_job a inner join bcode b on b.bcode = a.bcode  " + 
							"where employstatus='在职' and CONVERT(varchar(7), entrydate, 120) = '" + month + "'  " + 
							"and (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = b.bcode and convert(varchar(7),ChangeDate,120) > '" + month + "')) " + 
							") as entryNo on entryNo.systemid = onMonthSum.systemid";
	DataSet depLeaving =DST.getDataSet(depSql);
	DataSet internsLeaving = DST.getDataSet(internsSql);
	int expectedSum = 0;  
	int onSum = 0;
	int entrySum = 0;
	int leavSum = 0;
	int thisSum = 0;
	int vacanciesSum = 0;
%>
<%= month %>月部门流失率统计<br/>
  <table width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
    <tr bgcolor="#e8e8e0" align="center">
      <td>部门</td>
      <td>预计人数</td>
      <td>到上月底在职人数</td>
      <td>本月入职人数</td>
      <td>本月离职人数</td>
      <td>到本月底在职人数</td>
      <td>对比结果</td>
      <td>流失率</td>
    </tr>
<%
	for(int i=0; i<depLeaving.getRowCount(); i++) {
		expectedSum = expectedSum + Integer.parseInt(depLeaving.getValue(i, "expectedSum"));
		onSum = onSum + Integer.parseInt(depLeaving.getValue(i, "onSum"));
		entrySum = entrySum + Integer.parseInt(depLeaving.getValue(i, "entryNo"));
		leavSum = leavSum + Integer.parseInt(depLeaving.getValue(i, "leavNo"));
		thisSum = thisSum + Integer.parseInt(depLeaving.getValue(i, "thisSum"));
		vacanciesSum = vacanciesSum + Integer.parseInt(depLeaving.getValue(i, "vacancies"));
		double f = Double.parseDouble(depLeaving.getValue(i, "staff")) * 100;
		BigDecimal r = new BigDecimal(f).setScale(2,BigDecimal.ROUND_HALF_UP);
%>
    <tr>
      <td><%= depLeaving.getValue(i, "name") %></td>
      <td><%= depLeaving.getValue(i, "expectedSum") %></td>
      <td><%= depLeaving.getValue(i, "onSum") %></td>
      <td><%= depLeaving.getValue(i, "entryNo") %></td>
      <td><%= depLeaving.getValue(i, "leavNo") %></td>
      <td><%= depLeaving.getValue(i, "thisSum") %></td>
      <td><%= depLeaving.getValue(i, "vacancies") %></td>
      <td><%= r + "%" %></td>
    </tr>
<%
}

BigDecimal dep = new BigDecimal(leavSum*2.0/(onSum + thisSum)*100).setScale(2,BigDecimal.ROUND_HALF_UP);
%>
	<tr>
      <td>合计（不计实习生）</td>
      <td><%= expectedSum %></td>
      <td><%= onSum %></td>
      <td><%= entrySum %></td>
      <td><%= leavSum %></td>
      <td><%= thisSum %></td>
      <td><%= vacanciesSum %></td>
      <td><%= dep + "%" %></td>
    </tr>
 <%
double f = Double.parseDouble(internsLeaving.getValue(0, "staff")) * 100;
BigDecimal r = new BigDecimal(f).setScale(2,BigDecimal.ROUND_HALF_UP);

expectedSum = expectedSum + Integer.parseInt(internsLeaving.getValue(0, "expectedSum"));
onSum = onSum + Integer.parseInt(internsLeaving.getValue(0, "onSum"));
entrySum = entrySum + Integer.parseInt(internsLeaving.getValue(0, "entryNo"));
leavSum = leavSum + Integer.parseInt(internsLeaving.getValue(0, "leavNo"));
thisSum = thisSum + Integer.parseInt(internsLeaving.getValue(0, "thisSum"));
vacanciesSum = vacanciesSum + Integer.parseInt(internsLeaving.getValue(0, "vacancies"));
BigDecimal interns = new BigDecimal(leavSum*2.0/(onSum + thisSum)*100).setScale(2,BigDecimal.ROUND_HALF_UP);
 %>  
    <tr>
      <td>实习生</td>
      <td><%= internsLeaving.getValue(0, "expectedSum") %></td>
      <td><%= internsLeaving.getValue(0, "onSum") %></td>
      <td><%= internsLeaving.getValue(0, "entryNo") %></td>
      <td><%= internsLeaving.getValue(0, "leavNo") %></td>
      <td><%= internsLeaving.getValue(0, "thisSum") %></td>
      <td><%= internsLeaving.getValue(0, "vacancies") %></td>
      <td><%= r + "%" %></td>
    </tr>
    
    <tr>
      <td>合计（包括实习生）</td>
      <td><%= expectedSum %></td>
      <td><%= onSum %></td>
      <td><%= entrySum %></td>
      <td><%= leavSum %></td>
      <td><%= thisSum %></td>
      <td><%= vacanciesSum %></td>
      <td><%= interns + "%" %></td>
    </tr>
  </table>
</body>
</html>
